using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Infragistics.WebUI.UltraWebNavigator;
using Infragistics.WebUI.Misc; // For WebAsyncRefreshPanel
using System.Text;
using System.Collections;
using System.IO;


/// <summary>
/// Summary description for TreeViewI
/// </summary>
public class TreeViewI
{
    HDDatabase HDD;
    security HDS;
    DynamicDisplayI DDI;
    GenerateGrid objgg;
    GridPaging gpobj;
	public TreeViewI()
	{
		//
		// TODO: Add constructor logic here
		//
	}

    public bool GenerateTree(DataTable dtaPage, DataTable dtaPageSrch, DataTable dtaPageKeys, DataTable dtaPageControls, string strParentField, string strChildField, HtmlForm frmObj, bool bRead, bool bCreate, bool bEdit, bool bDelete, string strPagesetRowId)//Added BY DHanapal ON 30DEC2010
    {
        string strsql = "";
        string sFieldAlias = "";
        string strCondStr = "";
        gpobj = new GridPaging();

        string iTotalRecord = "";
        iTotalRecord = "0";

        strCondStr = strParentField + " is null or " + strParentField + " = " + "''";
        bool recordCountdetailsavail = false;
        string currentTotalRecordval = "";
        Hashtable hsDatatype = new Hashtable();

        #region Old Lines commented by 'Imran Gani' on 14-Mar-2014, to avoid top key from query and use ROW_NUMBER().
        //strsql = getSelectQuerywithPaging(dtaPage, dtaPageSrch, dtaPageKeys, dtaPageControls, strParentField, strChildField, strCondStr, ref sFieldAlias, ref iTotalRecord, recordCountdetailsavail, ref currentTotalRecordval, ref hsDatatype);
        //string Order = " desc";
        //string sOrderby = gpobj.getSortField(dtaPageControls, ref Order);
        //if (Order.Trim() == "asc")
        //{
        //    strsql = strsql.TrimEnd() + " order by " + sOrderby + " " + " desc" + " ,[keys] desc ";
        //}
        //else
        //{
        //    strsql = strsql.TrimEnd() + " order by " + sOrderby + " " + " asc" + " ,[keys] desc ";
        //}
        //sFieldAlias += "," + "keys";

        //strsql = "Select top " + GlobalValues.intNoOfRecordsPerPage.ToString() + " " + sFieldAlias + " from ( " + strsql + ") as tt order by " + sOrderby + " " + Order + " ,[keys] asc ";
        #endregion

        string Order = " desc";
        string sOrderby = gpobj.getSortField(dtaPageControls, ref Order);
        strsql = getSelectQuerywithPaging(dtaPage, dtaPageSrch, dtaPageKeys, dtaPageControls, strParentField, strChildField, strCondStr, ref sFieldAlias, ref iTotalRecord, recordCountdetailsavail, ref currentTotalRecordval, ref hsDatatype, sOrderby);
        
        /*************************/
        if (strsql != "")
        {
            // strsql += " and " + strParentField + " is null "; //Added By Dhanapal to get The Parent Node....
            try
            {
                HDD = new HDDatabase();
                DataSet dseObj = HDD.GetDataset(strsql);
                if (dseObj.Tables[0].Rows.Count > 0)
                {
                    /**********Added By Dhanapal For Bind Value Checking*********************/
                    for (int i = 0; i < dtaPage.Rows.Count; i++)
                    {
                        string strPageId, strSearchAttribName, strBindRowID;
                        strPageId = dtaPage.Rows[i]["page_id"].ToString();
                        //DataRow[] droPageSrch = dtaPageSrch.Select("page_id='" + strPageId.Replace("'", "''") + "' and list_flag=1 and control_attrib_id = '" + strParentField + "'", "search_seq");
                        DataRow[] droPageSrch = dtaPageSrch.Select("page_id='" + strPageId.Replace("'", "''") + "' and list_flag=1 ", "search_seq");
                        for (int j = 0; j < droPageSrch.Length; j++)
                        {
                            //strSearchAttribName = droPageSrch[j]["search_attrib_name"].ToString();
                            strSearchAttribName = droPageSrch[j]["search_attrib_id"].ToString();
                            if (strSearchAttribName == "")
                            {
                                strSearchAttribName = droPageSrch[j]["search_attrib_id"].ToString();
                            }
                            strBindRowID = droPageSrch[j]["control_bind_id"].ToString();

                            if (strBindRowID != "")
                            {
                                //string search_attrib_name = droPageSrch[j]["search_attrib_name"].ToString();
                                string search_attrib_name = droPageSrch[j]["search_attrib_id"].ToString();
                                string sBindStoreField = "", sBindDisplayField = "", sBindTable = "";
                                DataRow drBind = null;
                                if (strBindRowID != "") drBind = HDD.GetTableRow("select * from sy_bnd where af_row_id = '" + strBindRowID + "';");
                                if (drBind != null)
                                {
                                    sBindStoreField = drBind["bind_value"].ToString();
                                    sBindDisplayField = drBind["bind_text"].ToString();
                                    sBindTable = drBind["bind_table"].ToString();
                                    addBindColumn(dseObj, search_attrib_name);

                                    string af_row_ids = "";
                                    if (dseObj != null)
                                    {
                                        if (dseObj.Tables.Count > 0)
                                        {
                                            if (dseObj.Tables[0].Rows.Count > 0)
                                            {
                                                af_row_ids = getDistinct(dseObj.Tables[0], search_attrib_name);
                                            }
                                        }
                                    }
                                    string sSql = "";
                                    DataTable dt = new DataTable();
                                    if (af_row_ids != "")
                                    {
                                        sSql = "select " + sBindDisplayField + "," + sBindStoreField + " from " + sBindTable + " where " + sBindStoreField + " in (" + af_row_ids + ")";
                                    }
                                    else
                                    {
                                        sSql = "select " + sBindDisplayField + "," + sBindStoreField + " from " + sBindTable;
                                    }
                                    dt = HDD.GetDataTable(sSql, sBindTable);
                                    dseObj.Tables[0].Columns.Add(strSearchAttribName + "Bind_Storage_Value", typeof(string)); //To Maintain The Bind Id Value
                                    foreach (DataRow dr in dseObj.Tables[0].Rows)
                                    {
                                        if (dr[strSearchAttribName].ToString() == "") dr[strSearchAttribName] = "";
                                        else
                                        {
                                            DataRow[] drr = dt.Select("" + sBindStoreField + "= '" + dr[strSearchAttribName].ToString().Replace("'", "''") + "'");
                                            if (drr.Length == 0) dr[strSearchAttribName] = "Record Not Found";
                                            else
                                            {
                                                for (int l = 0; l < drr.Length; l++)
                                                {
                                                    if (!sBindDisplayField.Contains("+"))
                                                    {
                                                        dr[strSearchAttribName] = drr[l][sBindDisplayField].ToString();
                                                        dr[strSearchAttribName + "Bind_Storage_Value"] = drr[l][sBindStoreField].ToString();
                                                    }
                                                    else
                                                    {
                                                        try
                                                        {
                                                            dr[strSearchAttribName] = drr[l]["Column1"].ToString();
                                                        }
                                                        catch { dr[strSearchAttribName] = ""; }

                                                    }
                                                }
                                            }
                                        }
                                    }
                                    dt.Dispose();
                                    dt = null;

                                }
                            }
                        }
                        /***************End of the Changes For Bind*******************/
                        dseObj.Tables[0].TableName = dtaPage.Rows[0]["page_id"].ToString(); //Added By Dhanapal On Jan 10 2011

                        BuildParentNode(dseObj.Tables[0], frmObj, strParentField, strChildField, GlobalValues.intNoOfRecordsPerPage.ToString(), iTotalRecord, currentTotalRecordval, bRead, bCreate, bEdit, bDelete, strPagesetRowId, ref hsDatatype);
                        return true;
                    }
                }
                else
                {
                    DDI = new DynamicDisplayI();
                    System.Web.UI.HtmlControls.HtmlGenericControl p = (System.Web.UI.HtmlControls.HtmlGenericControl)DDI.FindControl(frmObj.Controls, "panelDynamicTree");
                    LiteralControl lc = new LiteralControl();
                    lc.Text = "<font class='controlFont'>No records available</font>";
                    p.Controls.Add(lc);
                        
                }

            }
            catch (Exception e1)
            {
                LogWriter.WriteLog("Error In GenerateTree : Build Tree.cs");

            }
            finally
            {

            }

        }

        return true;
    }

    protected void BuildParentNode(DataTable dt, HtmlForm frmObj, string strParentField, string strChildField, string RecordsPerPage, string iTotalRecords, string currentTotalRecordval, bool bRead, bool bCreate, bool bEdit, bool bDelete, string strPagesetRowId, ref Hashtable hsDatatype)
    {
        try
        {
            Int64 recordsPerPage = Convert.ToInt64(RecordsPerPage);
            Int64 iTotalRecordval = Convert.ToInt64(iTotalRecords);
            Int64 currentTotalRecordsCount = Convert.ToInt64(currentTotalRecordval);
            DDI = new DynamicDisplayI();

            /** Added By Dhanapal On Jan 03 2010******/
            WebAsyncRefreshPanel warp = new WebAsyncRefreshPanel();
            warp.ID = "UltraWebTreeAsyncHolder";
            warp.ClientSideEvents.InitializePanel = "UltraWebTreeAsyncHolder_InitializePanel";
            warp.LinkedRefreshControlID = "UltraWebTree1";
           

            /***************************************/

            UltraWebTree ut = new UltraWebTree();
            ut.ID = "UltraWebTree1";
            ut.SelectedNodeStyle.CssClass = "sample";
            ut.EnableViewState = true;
            //ut.ClientSideEvents.InitializeTree = "UltraWebTree_Menu_InitializeTree"; //Added By Dhanapal On Jan 19 2011
            HDD = new HDDatabase();
            string sEditPage = "";

            string strmnuId = HttpContext.Current.Request.QueryString["mn"].ToString() != "" ? HttpContext.Current.Request.QueryString["mn"].ToString() : "";
            GenerateGrid objGD = new GenerateGrid();
            sEditPage = objGD.getEditPage(HDD, strmnuId);
            objGD.loadCurrencySettings(); //To load the Currency Settings
            ArrayList currencyCode_Rate = objGD.currencyCode_Rate;


            string sUTC_Offset = DDI.getUserSetting("sy_usr", "time_zone", "sy_timezone", "utc_offset", "timezone_id");

            for (int i = 0; i < dt.Rows.Count; i++)
            {
                Node d = new Node();
                string key = dt.Rows[i]["keys"] != null ? dt.Rows[i]["keys"].ToString() : "";

                d.CssClass = "NodeText"; //Css For the Node Text
                d.LoadOnDemand = true;

                string strnodetextval = dt.Rows[i][0] != null ? dt.Rows[i][0].ToString() : "";

                if (hsDatatype.Contains(dt.Columns[0].ColumnName)) //To Covert according to dataType
                {
                    strnodetextval = getFormattedData(hsDatatype, dt.Columns[0].ColumnName, strnodetextval, sUTC_Offset, currencyCode_Rate);
                }
                
                string strEditaccess = getEditAccess(bCreate, bEdit, bDelete, sEditPage, strPagesetRowId, dt, key, strmnuId, dt.Rows[i], strnodetextval);


                if (dt.Columns[strChildField + "Bind_Storage_Value"] != null)
                {
                    //d.Text = strEditaccess +  dt.Rows[i][0].ToString();
                    d.Text = strEditaccess;
                    d.Tag = dt.Rows[i][strChildField + "Bind_Storage_Value"].ToString();
                }
                else
                {
                    //d.Text = strEditaccess + dt.Rows[i][0].ToString();
                    d.Text = strEditaccess;
                    d.Tag = dt.Rows[i][strChildField].ToString();
                }
                d.ShowExpand = true;
                ut.Nodes.Add(d);
                // ut.WebTreeTarget = WebTreeTarget.HierarchicalTree;
                ut.WebTreeTarget = WebTreeTarget.ClassicTree;

            }


            //if (currentTotalRecordsCount > recordsPerPage)
            if (iTotalRecordval > currentTotalRecordsCount) // Added by 'Imran Gani' on 14-Mar-2014, to compare total records with current records.
            {
                Node mnode = new Node();
                mnode.CssClass = "NodeText";
                mnode.Text = "More..";
                mnode.Tag = "PARENT_NODE" + "|" + " " + "|" + iTotalRecordval.ToString() + "|" + currentTotalRecordval.ToString() + "|" + recordsPerPage.ToString();
                mnode.LoadOnDemand = true;
                ut.Nodes.Add(mnode);
            }


            ut.ExpandAll();

            //ut.ClientSideEvents.NodeClick = "callme";
            //ut.ClientSideEvents.NodeExpand = "callme";

            //ut.EnableAppStyling = Infragistics.WebUI.Shared.DefaultableBoolean.True;

            //ut.CollapseImage = "~/Images/ig_treeXPPlus.gif";
            //ut.ExpandImage = "~/Images/treeExpand.gif";
            //ut.ClientSideEvents.NodeCollapse = "callme";
            //ut.BackColor = System.Drawing.Color.White;
            //ut.LoadOnDemand = LoadOnDemand.ManualSmartCallbacks;
            ut.LoadOnDemand = LoadOnDemand.Manual;
            ut.DemandLoad += new DemandLoadEventHandler(UltraWebTree1_DemandLoad);
            System.Web.UI.HtmlControls.HtmlGenericControl p = (System.Web.UI.HtmlControls.HtmlGenericControl)DDI.FindControl(frmObj.Controls, "panelDynamicTree");

            warp.Controls.Add(ut);
            p.Controls.Add(warp);
        }
        catch (Exception e1)
        {
            LogWriter.WriteLog("Error in BuildParentNode");
        }

    }



    private string GetSelectPageFields(DataTable dtaPage, DataTable dtaPageSrch, DataTable dtPageControls, string strChildField, ref string sFieldAliases, string key, ref Hashtable hsDatatype ,string strParentField)
    {
        security HDS = new security();
        //GenerateGrid gg = new GenerateGrid();
        string strSearchAttribName = "";
        string strSelectFlds = "";
        //string sFieldAliases = "";
        string strPageId = "";
        string strTableName = "";
        string sObject_id = "";
        string sObject_af_row_id = "";

     
            try
            {
                for (int i = 0; i < dtaPage.Rows.Count; i++)
                {
                    strPageId = dtaPage.Rows[i]["page_id"].ToString();
                    strTableName = dtaPage.Rows[i]["page_table_name"].ToString();
                    sObject_id = dtaPage.Rows[i]["object_id"].ToString();
                    sObject_af_row_id = getObject_af_row_id(sObject_id);

                    DataRow[] droPageSrch;
                    //DataRow[] droPage
                    DataTable droPageSrchRows;
                    droPageSrchRows = dtaPageSrch; //Assigninh
                    if (!HDS.getObjectRightsForUser(sObject_id, "read"))
                        continue;
                    if (key == "PARENT")
                    {
                        //droPageSrch = dtaPageSrch.Select("page_id='" + strPageId.Replace("'", "''") + "' and list_flag=1 and search_attrib_id = '" + parentField.Trim() + "'", "search_seq");
                        //droPageSrch = dtaPageSrch.Select("page_id='" + strPageId.Replace("'", "''") + "'", "search_seq");
                        droPageSrch = dtaPageSrch.Select("page_id='" + strPageId.Replace("'", "''") + "'  and list_flag=1", "search_seq");
                        //droPageSrch = dtaPageSrch.Select("page_id='" + strPageId.Replace("'", "''") + "' and list_flag=1 or search_attrib_id = '" + strParentField.Trim() + "' or search_attrib_id = '" + strChildField.Trim() + "'", "search_seq");

                    }
                    else
                    {
                        // droPageSrch = dtaPageSrch.Select("page_id='" + strPageId.Replace("'", "''") + "' and list_flag=1 and search_attrib_id = '" + parentField.Trim() + "'", "search_seq");
                        droPageSrch = dtaPageSrch.Select("page_id='" + strPageId.Replace("'", "''") + "'  and list_flag=1", "search_seq");
                        //droPageSrch = dtaPageSrch.Select("page_id='" + strPageId.Replace("'", "''") + "' and list_flag=1 or search_attrib_id = '" + strParentField.Trim() + "' or search_attrib_id = '" + strChildField.Trim() + "'", "search_seq");
                    }

                    
                   // droPageSrchRows[0] = droPageSrch[0];

                    HDD = new HDDatabase();

                   


                    //
                    for (int j = 0; j < droPageSrch.Length; j++)
                    {
                        //strSearchAttribName = droPageSrch[j]["search_attrib_name"].ToString(); //Commented By Dhanpal to maintain original Id
                        strSearchAttribName = droPageSrch[j]["search_attrib_id"].ToString();
                        if (strSearchAttribName == "")
                        {
                            strSearchAttribName = droPageSrch[j]["search_attrib_id"].ToString();
                        }
                        if (strSelectFlds != "")
                        {
                            strSelectFlds += ", ";
                        }
                        if (sFieldAliases != "")
                        {
                            sFieldAliases += ", ";
                        }
                        string strBindRowID = droPageSrch[j]["control_bind_id"].ToString();
                        //strSelectFlds += "[" + strPageId + "].[" + droPageSrch[j]["search_attrib_id"].ToString() + "] as [" + strSearchAttribName + "]";
                        //sFieldAliases += "[" + strSearchAttribName + "]";
                        strSelectFlds += "[" + strPageId + "].[" + droPageSrch[j]["search_attrib_id"].ToString() + "] as [" + droPageSrch[j]["search_attrib_id"].ToString() + "]";
                        sFieldAliases += "[" + droPageSrch[j]["search_attrib_id"].ToString() + "]";

                        if (!hsDatatype.Contains(strSearchAttribName))
                            hsDatatype.Add(strSearchAttribName, droPageSrch[j]["control_attrib_type"].ToString());
                        //hsDatatype.Add(strSearchAttribName, droPageSrch[j]["control_attrib_id"].ToString()); //To maintain the Id of the Column
                    }

                    appendParentAndchildField(strParentField, strChildField, strPageId, droPageSrchRows, dtPageControls, ref strSelectFlds, ref sFieldAliases); // This check whether the Pagr_srh contains the parent row and child row if not then retreive and append from page control


                    if (key != "PARENT") //Because Distinct Parent Column Only Should be Added 
                    {
                        string sOrderby = "modified_at";
                        if (dtPageControls.Select("control_attrib_id='modified_at'").Length > 0)
                        {
                            sOrderby = "modified_at";
                        }
                        else
                        {
                            if (dtPageControls.Select("control_attrib_id='modified_date'").Length > 0)
                                sOrderby = "modified_date";

                        }
                        strSelectFlds += ", " + sOrderby;

                    }
                }
            }
            catch (Exception e1)
            {
                LogWriter.WriteLog("Error In GetSelectedParentField :Build Tree.cs");
            }
            finally
            {
                HDS = null;
            }
        return strSelectFlds;
    }

    private void appendParentAndchildField(string strParentField, string strChildField, string strPageId, DataTable droPageSrch, DataTable dtPageControls , ref string strSelectFlds, ref string sFieldAliases) //Added By Dhanapal On jan 07 2010
    {
        try
        {
            string page_id = HDD.GetColumnValue("Select af_row_id  from sy_pg where page_id = '" + strPageId.Replace("'", "''") + "'");
            string strFields = "";
            //droPageSrchRows[1] = dtPageControls.Select("page_id = '" + page_id + "' and control_id = '" + strParentField + "'");
            DataRow[] drrObj;
            drrObj = droPageSrch.Select("search_attrib_id = '" + strParentField + "' and list_flag =1 ");
            if (drrObj.Length < 1)
            {
                if (strSelectFlds != "")
                {
                    strSelectFlds += ", ";
                }
                if (sFieldAliases != "")
                {
                    sFieldAliases += ", ";
                }
                drrObj = dtPageControls.Select("control_id = '" + strParentField + "'");
                strSelectFlds += "[" + strPageId + "].[" + drrObj[0]["control_attrib_id"].ToString() + "] as [" + drrObj[0]["control_attrib_id"].ToString() + "]";
                sFieldAliases += "[" + drrObj[0]["control_attrib_id"].ToString() + "]";
            }
            drrObj = droPageSrch.Select("search_attrib_id = '" + strChildField + "' and list_flag =1 ");
            if (drrObj.Length < 1)
            {
                if (strSelectFlds != "")
                {
                    strSelectFlds += ", ";
                }
                if (sFieldAliases != "")
                {
                    sFieldAliases += ", ";
                }
                drrObj = dtPageControls.Select("control_id = '" + strChildField + "'");
                strSelectFlds += "[" + strPageId + "].[" + drrObj[0]["control_attrib_id"].ToString() + "] as [" + drrObj[0]["control_attrib_id"].ToString() + "]";
                sFieldAliases += "[" + drrObj[0]["control_attrib_id"].ToString() + "]";
            }
        }
        catch (Exception e1)
        {
            LogWriter.WriteLog(" Error in appendParentandCHildColumn ");
        }
       
    }

    private string getObject_af_row_id(string Object_id)
    {
        HDDatabase HDD = new HDDatabase();
        return HDD.GetColumnValue("select af_row_id from sy_obj where object_id='" + Object_id + "'");
    }

    protected void UltraWebTree1_DemandLoad(object sender, Infragistics.WebUI.UltraWebNavigator.WebTreeNodeEventArgs e)
    {
        //System.Threading.Thread.Sleep(5000);
        UltraWebTree ut = (UltraWebTree)sender;
        bool RecordCountDetailsavail = false;
        string strNodeType = "";
        string strnodeText = "";
        string strNodeTotalCount = "";
        string strNodeNoOfRecordsperpage = "";
        string strnodeTextval = "";
        string currentTotalRecordval = "";

        GridPaging gpobj = new GridPaging();
        try
        {
            if (e.Node.Nodes.Count > 0) //To prevent appending of childNodes aftr back button click
                e.Node.Nodes.Clear();
            strnodeText = e.Node.Tag == null ? "" : (string)e.Node.Tag;
            strnodeTextval = e.Node.Text == null ? "" : (string)e.Node.Text;
            //string strnodeText = e.Node.Text == null ? "" : (string)e.Node.Text;
            //HiddenField hfParentfield = (HiddenField)DDI.FindControl(ut.Parent.Controls, "hdnParentField");
            //HiddenField hfChildField = (HiddenField)DDI.FindControl(ut.Parent.Controls, "hdnChildField");
            //HiddenField hfPagesetid = (HiddenField)DDI.FindControl(ut.Parent.Controls, "hdnPageSetId");

            HiddenField hfParentfield = (HiddenField)DDI.FindControl(ut.Parent.Parent.Controls, "hdnParentField");
            HiddenField hfChildField = (HiddenField)DDI.FindControl(ut.Parent.Parent.Controls, "hdnChildField");
            HiddenField hfPagesetid = (HiddenField)DDI.FindControl(ut.Parent.Parent.Controls, "hdnPageSetId");

            string strParentField = hfParentfield == null ? "" : hfParentfield.Value;
            string strChildField = hfChildField == null ? "" : hfChildField.Value;
            string strPagesetRowId = hfPagesetid == null ? "" : hfPagesetid.Value;
            if (strnodeTextval.Trim() != "")
            {
                if (strnodeTextval.ToUpper().StartsWith("MORE.."))
                {
                    string[] strNodeDetail = strnodeText.Split('|');
                    if (strNodeDetail.Length > 0)
                    {
                        RecordCountDetailsavail = true;
                        strNodeType = strNodeDetail[0].ToString();
                        strnodeText = strNodeDetail[1].ToString();
                        strNodeTotalCount = strNodeDetail[2].ToString();
                        currentTotalRecordval = strNodeDetail[3].ToString();
                        strNodeNoOfRecordsperpage = strNodeDetail[4].ToString();
                        //BuildMoreChildNodes(strNodeType, strNodeId, strNodeTotalCount,);
                        //e.Node.Parent.Nodes.Remove(e.Node); // To Remove The 
                    }
                    else
                    {
                        //RecordDetailsavail = false;
                        strNodeType = "";
                        strnodeText = "";
                        strNodeTotalCount = "";
                        currentTotalRecordval = "";
                        strNodeNoOfRecordsperpage = "";
                    }
                }

                security HDS = new security();
                bool bRead = false;
                bool bCreate = false;
                bool bEdit = false;
                bool bDelete = false;
                HDS.getPagesetRights(strPagesetRowId, ref bRead, ref bCreate, ref bEdit, ref bDelete);
                if (bRead)//If read Permission Then Proced
                {
                    PageInfoLoad PIL = new PageInfoLoad();
                    DataSet dsePageObj = PIL.GetPageSetPagesSrchInfo(strPagesetRowId);
                    DataTable dtaPageSet = dsePageObj.Tables["PageSet"];
                    DataTable dtaPage = dsePageObj.Tables["Page"];
                    DataTable dtaPageKeys = dsePageObj.Tables["PageKeys"];
                    DataTable dtaPageSrch = dsePageObj.Tables["PageSrch"];
                    DataTable dtaPageControls = dsePageObj.Tables["Page Controls"];
                    DataTable dtaPageMode = dsePageObj.Tables["PageModes"];
                    DataTable dtaPageModeFields = dsePageObj.Tables["PageModesFields"];
                    string sNoofRecords = this.HDD.getAcrMemValue("3813ac7202378893", "1");
                    if (sNoofRecords != "" && sNoofRecords != "0")
                        GlobalValues.intNoOfRecordsPerPage = int.Parse(sNoofRecords);
                    else
                        GlobalValues.intNoOfRecordsPerPage = 8;
                    string strsql = "";
                    //DataRow[] dr = dtaPageSrch.Select("search_attrib_id = '" + strParentField + "'");
                    DataRow[] dr = dtaPageSrch.Select(" ");
                    string strPageId = dr.Length != 0 ? dr[0]["Page_id"].ToString() : "";
                    //string strPageId = getPageId(strPagesetRowId);
                    string strCondStr = "";

                    if (strNodeType == "PARENT_NODE") //IF the Parent Node More is CLicked
                    {
                        strCondStr = "[" + strPageId + "]" + "." + "[" + strParentField + "]" + " is null ";
                    }
                    else
                    {
                        strCondStr = "[" + strPageId + "]" + "." + "[" + strParentField + "]" + " = '" + strnodeText + "'"; //This condition satisfies for more and remaining load click
                    }

                    string sFieldAlias = "";
                    string iTotalRecord = "";
                    //currentTotalRecordval = "";
                    Hashtable hsDatatype = new Hashtable();

                    #region Old Lines commented by 'Imran Gani' on 14-Mar-2014, to avoid top key from query and use ROW_NUMBER().
                    //strsql = getSelectQuerywithPaging(dtaPage, dtaPageSrch, dtaPageKeys, dtaPageControls, strParentField, strChildField, strCondStr, ref sFieldAlias, ref iTotalRecord, RecordCountDetailsavail, ref currentTotalRecordval, ref hsDatatype);
                    ////strsql = gpobj.PageSetSelectSql( dtaPage,  dtaPageKeys,  dtaPageSrch, ref  dtaPageControls, "",  "", "", "",  "","",  "", "");
                    ////strSql = "Select top " + GlobalValues.intNoOfRecordsPerPage.ToString() + " " + sFieldAlias + " from ( " + strSql + ") as tt order by " + sOrderby + " " + Order + " ,[keys] asc ";


                    //string Order = " desc";
                    //string sOrderby = gpobj.getSortField(dtaPageControls, ref Order);
                    //if (Order.Trim() == "asc")
                    //{
                    //    strsql = strsql.TrimEnd() + " order by " + sOrderby + " " + " desc" + " ,[keys] desc ";
                    //}
                    //else
                    //{
                    //    strsql = strsql.TrimEnd() + " order by " + sOrderby + " " + " asc" + " ,[keys] desc ";
                    //}
                    //sFieldAlias += "," + "keys";

                    //strsql = "Select top " + GlobalValues.intNoOfRecordsPerPage.ToString() + " " + sFieldAlias + " from ( " + strsql + ") as tt order by " + sOrderby + " " + Order + " ,[keys] asc ";
                    #endregion

                    string Order = " desc";
                    string sOrderby = gpobj.getSortField(dtaPageControls, ref Order);
                    strsql = getSelectQuerywithPaging(dtaPage, dtaPageSrch, dtaPageKeys, dtaPageControls, strParentField, strChildField, strCondStr, ref sFieldAlias, ref iTotalRecord, RecordCountDetailsavail, ref currentTotalRecordval, ref hsDatatype, sOrderby);
                    DataSet dsObj = getFinalQueryDataset(dtaPage, dtaPageSrch, strsql, strChildField, "TREE");

                    dsObj.Tables[0].TableName = dtaPage.Rows[0]["page_id"].ToString(); //Added By Dhanapal On Jan 10 2011
                    
                    if (dsObj != null)
                    {
                        if (dsObj.Tables[0].Rows.Count > 0)
                        {
                            if (strNodeType == "PARENT_NODE") //More Parent Node
                            {
                                BuildMoreParentNode(dsObj, iTotalRecord, ut, e.Node, strChildField, strPagesetRowId, strnodeText, currentTotalRecordval, bRead, bCreate, bEdit, bDelete, ref hsDatatype);
                            }
                            else if (strNodeType == "MORE_NODE")
                            {
                                if (e.Node.Parent != null)
                                    e.Node.Parent.Nodes.Remove(e.Node);
                                BuildChildNodes(dsObj, iTotalRecord, e.Node.Parent, bCreate, bEdit, bDelete, strChildField, strPagesetRowId, strnodeText, currentTotalRecordval, ref hsDatatype);
                            }
                            else
                            {
                                BuildChildNodes(dsObj, iTotalRecord, e.Node, bCreate, bEdit, bDelete, strChildField, strPagesetRowId, strnodeText, currentTotalRecordval, ref hsDatatype);
                            }
                        }
                        else
                        {
                            Node emptynode = new Node();
                            emptynode.CssClass = "NodeText";
                            emptynode.Text = "No records available.";
                            e.Node.Nodes.Add(emptynode);

                        }
                    }

                }

            }
        }
        catch (Exception e1)
        {
            LogWriter.WriteLog("Error in UltraWebTree DemandOnLoad");
        }
    }


    private void BuildMoreParentNode(DataSet dsObj, string iTotalRecord, Infragistics.WebUI.UltraWebNavigator.UltraWebTree ut, Infragistics.WebUI.UltraWebNavigator.Node mnode, string strChildField, string strPagesetRowId, string strnodeText, string currentTotalRecordval, bool bRead, bool bCreate, bool bEdit, bool bDelete, ref Hashtable hsDatatype)
    {
        ut.Nodes.Remove(mnode);
        string strnodeval = "";
        string sEditPage = "";
        string strmnuId = HttpContext.Current.Request.QueryString["mn"].ToString() != "" ? HttpContext.Current.Request.QueryString["mn"].ToString() : "";
        HDD = new HDDatabase();
        DataTable dtaObj = null;
        GenerateGrid objGD = new GenerateGrid();
        DDI = new DynamicDisplayI();
        dtaObj = dsObj.Tables[0];
        sEditPage = objGD.getEditPage(HDD, strmnuId);

        objGD.loadCurrencySettings(); //To load the Currency Settings
        ArrayList currencyCode_Rate = objGD.currencyCode_Rate;

        string sUTC_Offset = DDI.getUserSetting("sy_usr", "time_zone", "sy_timezone", "utc_offset", "timezone_id");

        foreach (DataRow dr in dsObj.Tables[0].Rows)
        {
            string key = dr["keys"] != null ? dr["keys"].ToString() : "";
            //if (dr[strChildField + "Bind_Storage_Value"] != null) //To store the field storage value
            if (dr.Table.Columns.Contains(strChildField + "Bind_Storage_Value"))
            {
                strnodeval = dr[strChildField + "Bind_Storage_Value"] != null ? dr[strChildField + "Bind_Storage_Value"].ToString() : "";
            }
            else
            {
                strnodeval = dr[strChildField] != null ? dr[strChildField].ToString() : "";
            }

            string strnodetextval = dr[0] != null ? dr[0].ToString() : "";

            if (hsDatatype.Contains(dsObj.Tables[0].Columns[0].ColumnName)) //To Covert according to dataType
            {
                strnodetextval = getFormattedData(hsDatatype, dsObj.Tables[0].Columns[0].ColumnName, strnodetextval, sUTC_Offset, currencyCode_Rate);
            }

            string strEditaccess = getEditAccess(bCreate, bEdit, bDelete, sEditPage, strPagesetRowId, dtaObj, key, strmnuId, dr, strnodetextval);

            if (strnodeval != "")
            {
                Node cnode = new Node();
                cnode.CssClass = "NodeText";
                //cnode.Text = strnodetextval;
                cnode.Text = strEditaccess;
                cnode.Tag = strnodeval;
                cnode.LoadOnDemand = true;
                ut.Nodes.Add(cnode);
            }

        }
        //if (Convert.ToInt64(iTotalRecord) > GlobalValues.intNoOfRecordsPerPage)
        //if (Convert.ToInt64(currentTotalRecordval) > GlobalValues.intNoOfRecordsPerPage)
        if (Convert.ToInt64(iTotalRecord) > Convert.ToInt64(currentTotalRecordval)) // Added by 'Imran Gani' on 14-Mar-2014, to compare total records with current records.
        {
            string currentParentText = "";
            Node morenode = new Node();
            morenode.CssClass = "NodeText";
            morenode.LoadOnDemand = true;
            morenode.Text = "More..";
            morenode.Tag = "PARENT_NODE" + "|" + currentParentText + "|" + iTotalRecord.ToString() + "|" + currentTotalRecordval.ToString() + "|" + GlobalValues.intNoOfRecordsPerPage.ToString();
            ut.Nodes.Add(morenode);
        }
    }

    private void BuildChildNodes(DataSet dsobj, string iTotalRecord, Infragistics.WebUI.UltraWebNavigator.Node pnode, bool bCreate, bool bEdit, bool bDelete, string strChildField, string strPagesetRowId, string currentParentText, string currentTotalRecordval, ref Hashtable hsDatatype)
    {
        string strmnuId = HttpContext.Current.Request.QueryString["mn"].ToString() != "" ? HttpContext.Current.Request.QueryString["mn"].ToString() : "";
        //n1.Text = strmnuId + "---" + strChildField;
        HDD = new HDDatabase();
        DataTable dtaObj = null;
        GenerateGrid objGD = new GenerateGrid();
        DDI = new DynamicDisplayI();
        string sEditPage = "";
        string strnodeval = "";
        dtaObj = dsobj.Tables[0];
        sEditPage = objGD.getEditPage(HDD, strmnuId);

        objGD.loadCurrencySettings(); //To load the Currency Settings
        ArrayList currencyCode_Rate = objGD.currencyCode_Rate;


        string sUTC_Offset = DDI.getUserSetting("sy_usr", "time_zone", "sy_timezone", "utc_offset", "timezone_id");

        foreach (DataRow dr in dsobj.Tables[0].Rows)
        {
            Node cnode = new Node();
            string key = dr["keys"] != null ? dr["keys"].ToString() : "";
            if (dr.Table.Columns.Contains(strChildField + "Bind_Storage_Value")) //To store the field storage value
            {
                strnodeval = dr[strChildField + "Bind_Storage_Value"] != null ? dr[strChildField + "Bind_Storage_Value"].ToString() : "";
            }
            else
            {
                strnodeval = dr[strChildField] != null ? dr[strChildField].ToString() : "";

            }
            string strnodetextval = dr[0] != null ? dr[0].ToString() : "";

            if (hsDatatype.Contains(dsobj.Tables[0].Columns[0].ColumnName)) //To Covert according to dataType
            {
                strnodetextval = getFormattedData(hsDatatype, dsobj.Tables[0].Columns[0].ColumnName, strnodetextval, sUTC_Offset, currencyCode_Rate);
            }

            if (strnodeval != "")
            {
                string strEditaccess = getEditAccess(bCreate, bEdit, bDelete, sEditPage, strPagesetRowId, dtaObj, key, strmnuId, dr, strnodetextval);
                //cnode.Text = strEditaccess + strnodetextval;
                cnode.Text = strEditaccess;
                cnode.Tag = strnodeval;
                cnode.CssClass = "NodeText";
                cnode.LoadOnDemand = true;
                pnode.Nodes.Add(cnode);  // Child Nodes get Appended here
            }
        }
        //if (Convert.ToInt64(iTotalRecord) > GlobalValues.intNoOfRecordsPerPage)
        //if (Convert.ToInt64(currentTotalRecordval) > GlobalValues.intNoOfRecordsPerPage)
        if (Convert.ToInt64(iTotalRecord) > Convert.ToInt64(currentTotalRecordval)) // Added by 'Imran Gani' on 14-Mar-2014, to compare total records with current records.
        {
            Node mnode = new Node();
            mnode.LoadOnDemand = true;
            mnode.CssClass = "NodeText";
            mnode.Text = "More..";
            mnode.Tag = "MORE_NODE" + "|" + currentParentText + "|" + iTotalRecord.ToString() + "|" + currentTotalRecordval.ToString() + "|" + GlobalValues.intNoOfRecordsPerPage.ToString();
            pnode.Nodes.Add(mnode);
        }
    }

    private string getFormattedData(Hashtable hsDatatype, string strcolname, string data, string sUTC_Offset, ArrayList currencyCode_Rate)
    {
        string strText = data;
        try
        {
            #region Old code commented by 'Imran Gani' on 28-Feb-2014, for MBLocale
            //DDI = new DynamicDisplayI();
            //objgg = new GenerateGrid();


            ////string sUTC_Offset = DDI.getUserSetting("sy_usr", "time_zone", "sy_timezone", "utc_offset", "timezone_id");
            //if (hsDatatype[strcolname].ToString().ToLower() == "dt")
            //{
            //    if (strcolname.ToLower().Trim() == "modified at" || strcolname.ToLower().Trim() == "created at")
            //    {
            //        if (strText != "" && strText != "&nbsp;")
            //        {
            //            if (strText != "" && strText != "&nbsp;")
            //            {
            //                strText = DDI.getUsersDT(strText, sUTC_Offset);
            //                Localization loc = new Localization();
            //                strText = loc.getDateTime4TimeZone(strText, sUTC_Offset, false, true);
            //            }
            //        }
            //    }
            //    else
            //    {
            //        if (strText != "" && strText != "&nbsp;")
            //            if (strText != "" && strText != "&nbsp;")
            //                strText = DDI.getUsersDT(strText, sUTC_Offset);
            //    }
            //}

            //else if (hsDatatype[strcolname].ToString().ToLower() == "cu")//sanal
            //{
            //    if (strText != "&nbsp;")
            //        strText = DDI.manageCurrency(strText, currencyCode_Rate);
            //}
            //else if (hsDatatype[strcolname].ToString().ToLower() == "da")
            //{
            //    if (strText != "" && strText != "&nbsp;")
            //        strText = objgg.manageDateTimeValues("da", strText);

            //}
            ////DDI = null;
            #endregion

            #region New code Added by 'Imran Gani' on 28-Feb-2014, to use switch case with MBLocale
            Localization loc = new Localization();
            strText = loc.getLocaleFormattedValue(strText, hsDatatype[strcolname].ToString(), strcolname, sUTC_Offset, DDI, currencyCode_Rate);
            loc = null;
            #endregion
        }
        catch (Exception e1)
        {
            LogWriter.WriteLog("Error in DataFormatting");
        }
        return strText;
    }

    private string getEditAccess(bool bCreate, bool bEdit, bool bDelete, string sEditPage, string strPagesetRowId, DataTable dtaObj, string strKey, string strMenuRowId, DataRow dr, string strFormatText)
    {
        objgg = new GenerateGrid();
        bool bEditOverride = true;
        bool bDeleteOverride = false;
        String sEditKeyValues = "";
        String sDeleteKeyValues = "";
        String sEdit_RowId = "";
        String sDelete_RowId = "";
        string strText = "";
        string strAcess = "";
        objgg.getOverrideAccess(ref bEditOverride, ref bDeleteOverride, ref dtaObj, ref sEditKeyValues, ref sDeleteKeyValues, ref sEdit_RowId, ref sDelete_RowId);

        if (bCreate || bEdit)
        {
            if (sEditKeyValues.Contains(strText))
            {
                if (bEditOverride)
                {
                    strText = @"" + sEditPage + @"?pgs_id=" + strPagesetRowId + "&PK=" + HttpUtility.UrlEncode(strKey)
                        + "&mn=" + strMenuRowId + @"";
                    strText = objgg.addMasterPrimaryKey(strText);
                }
                else
                {
                    strText = @"" + "NoAccess.aspx" + @"?pgs_id=" + strPagesetRowId + "&PK=" + sEdit_RowId
                        + "&mn=" + strMenuRowId + @"";
                }
            }
            else
            {
                strText = @"" + sEditPage + @"?pgs_id=" + strPagesetRowId + "&PK=" + HttpUtility.UrlEncode(strKey)
                    + "&mn=" + strMenuRowId + @"";
                strText = objgg.addMasterPrimaryKey(strText);
            }

            strText = "<a href=" + "'" + strText + "'" + " style='text-decoration:none;' title='Edit' ><img src='Images/Edit.gif' border='0' style='vertical-align:middle'></a>";

            strAcess += strText;
        }

        if (bCreate)
        {

            strText = @"" + sEditPage + @"?pgs_id=" + strPagesetRowId + "&PK=" + HttpUtility.UrlEncode(strKey)
                + "&mn=" + strMenuRowId + @"&CPY=0";

            strText = objgg.addMasterPrimaryKey(strText);

            strText = "<a href=" + "'" + strText + "'" + " style='text-decoration:none;' title='Copy'><img src='Images/copy.gif' border='0' style='vertical-align:middle'></a>";
            //lnkcopy.ImageUrl = "images/copy.gif";
            //lnkcopy.PostBackUrl = strText;
            strAcess += strText;
        }

        if (bDelete)
        {
            string nodetext = dr[0] != null ? dr[0].ToString() : "";
            int intCurrentPage = 0;
            //  htcc = new HtmlTableCell();
            //strText = "<a style='color:Blue;' ><u>Delete</u></a>";
            strText = "";
            string sTemp = HttpContext.Current.Request.Url.AbsoluteUri.Substring(0, HttpContext.Current.Request.Url.AbsoluteUri.LastIndexOf('/'));
            sTemp = sTemp + HttpContext.Current.Request.Url.AbsolutePath.Substring(HttpContext.Current.Request.Url.AbsolutePath.LastIndexOf("/"), (HttpContext.Current.Request.Url.AbsolutePath.Length - HttpContext.Current.Request.Url.AbsolutePath.LastIndexOf("/")));
            sTemp = sTemp + "?pgs_id=" + strPagesetRowId + "&DPK=" + strKey + "&mn=" + strMenuRowId + "&cp=" + intCurrentPage;


            sTemp = objgg.addMasterPrimaryKey(sTemp);
            strText = "<img src='Images/Delete.gif' border='0' title='Delete' style='vertical-align:middle;cursor:pointer;' " + " onclick= \"DeleteTreeNode(this,'" + sTemp + "','" + HttpUtility.HtmlEncode(nodetext) + "');\" " + ">";

            strAcess += strText;

        }


        strText = "";
        strText = strFormatText; // The Formateed Data
        string sViewPage = @"<a style='cursor:hand;text-decoration:none;' href=""" + sEditPage + "?pgs_id=" + strPagesetRowId + "&PK=" + HttpUtility.UrlEncode(strKey.ToString()) + "&MK=" + HttpContext.Current.Request.QueryString["PK"] + @""
            + "&mn=" + strMenuRowId + @"&VMODE=1"">" + HttpUtility.HtmlEncode(strText) + "</a>";
        //strText = "&nbsp;" + sViewPage;
        strText = sViewPage; // modified by srivatsan
        strAcess += strText;

        return strAcess;
    }

    public DataSet getFinalQueryDataset(DataTable dtaPage, DataTable dtaPageSrch, string finalQuery, string strChildField ,string key)
    {
        HDD = new HDDatabase();
        DataSet dseObj = HDD.GetDataset(finalQuery);
        if (dseObj.Tables[0].Rows.Count > 0)
        {
            for (int i = 0; i < dtaPage.Rows.Count; i++)
            {
                string strPageId, strSearchAttribName, strBindRowID;
                strPageId = dtaPage.Rows[i]["page_id"].ToString();
                DataRow[] droPageSrch = null;
                if (key.Trim() == "TREE")
                {
                     droPageSrch = dtaPageSrch.Select("page_id='" + strPageId.Replace("'", "''") + "' and list_flag=1 and control_attrib_id = '" + strChildField + "'", "search_seq");
                }
                else
                {
                     droPageSrch = dtaPageSrch.Select("page_id='" + strPageId.Replace("'", "''") + "' and list_flag=1 ", "search_seq");
                }
                for (int j = 0; j < droPageSrch.Length; j++)
                {
                    //strSearchAttribName = droPageSrch[j]["search_attrib_name"].ToString();
                    strSearchAttribName = droPageSrch[j]["search_attrib_id"].ToString();
                    if (strSearchAttribName == "")
                    {
                        strSearchAttribName = droPageSrch[j]["search_attrib_id"].ToString();
                    }
                    strBindRowID = droPageSrch[j]["control_bind_id"].ToString();

                    if (strBindRowID != "")
                    {
                        //string search_attrib_name = droPageSrch[j]["search_attrib_name"].ToString();
                        string search_attrib_name = droPageSrch[j]["search_attrib_id"].ToString();
                        string sBindStoreField = "", sBindDisplayField = "", sBindTable = "";
                        DataRow drBind = null;
                        if (strBindRowID != "") drBind = HDD.GetTableRow("select * from sy_bnd where af_row_id = '" + strBindRowID + "';");
                        if (drBind != null)
                        {
                            sBindStoreField = drBind["bind_value"].ToString();
                            sBindDisplayField = drBind["bind_text"].ToString();
                            sBindTable = drBind["bind_table"].ToString();
                            addBindColumn(dseObj, search_attrib_name);

                            string af_row_ids = "";
                            if (dseObj != null)
                            {
                                if (dseObj.Tables.Count > 0)
                                {
                                    if (dseObj.Tables[0].Rows.Count > 0)
                                    {
                                        af_row_ids = getDistinct(dseObj.Tables[0], search_attrib_name);
                                    }
                                }
                            }
                            string sSql = "";
                            DataTable dt = new DataTable();
                            if (af_row_ids != "")
                            {
                                sSql = "select " + sBindDisplayField + "," + sBindStoreField + " from " + sBindTable + " where " + sBindStoreField + " in (" + af_row_ids + ")";
                            }
                            else
                            {
                                sSql = "select " + sBindDisplayField + "," + sBindStoreField + " from " + sBindTable;
                            }
                            dt = HDD.GetDataTable(sSql, sBindTable);
                            
                            if ( key.Trim() == "TREE" )
                                dseObj.Tables[0].Columns.Add(strSearchAttribName + "Bind_Storage_Value", typeof(string)); //To Maintain The Bind Id Value
                            foreach (DataRow dr in dseObj.Tables[0].Rows)
                            {
                                if (dr[strSearchAttribName].ToString() == "") dr[strSearchAttribName] = "";
                                else
                                {
                                    DataRow[] drr = dt.Select("" + sBindStoreField + "= '" + dr[strSearchAttribName].ToString().Replace("'", "''") + "'");
                                    if (drr.Length == 0) dr[strSearchAttribName] = "Record Not Found";
                                    else
                                    {
                                        for (int l = 0; l < drr.Length; l++)
                                        {
                                            if (!sBindDisplayField.Contains("+"))
                                            {
                                                dr[strSearchAttribName] = drr[l][sBindDisplayField].ToString();
                                                if (key.Trim() == "TREE")
                                                    dr[strSearchAttribName + "Bind_Storage_Value"] = drr[l][sBindStoreField].ToString();
                                            }
                                            else
                                            {
                                                try
                                                {
                                                    dr[strSearchAttribName] = drr[l]["Column1"].ToString();
                                                }
                                                catch { dr[strSearchAttribName] = ""; }

                                            }
                                        }
                                    }
                                }
                            }
                            dt.Dispose();
                            dt = null;

                        }
                    }
                }


            }
        }
        return dseObj;
    }

    private string getDistinct(DataTable dt, string field) // Get distinct values from the datatable for the field as string
    {
        StringBuilder sb = new StringBuilder();
        string result = "";
        try
        {
            if (dt != null)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    if (dr[field].ToString() != "")
                    {
                        if (!sb.ToString().Contains("'" + dr[field].ToString() + "'"))
                        {
                            sb.Append("'" + dr[field].ToString().Replace("'", "''") + "',");
                        }
                    }
                }
                if (sb.ToString() != "")
                {
                    result = sb.ToString().Substring(0, sb.Length - 1);
                }
            }
        }
        catch (Exception ex)
        {
            LogWriter.WriteLog("Error : GetDistinctRecords :" + ex.Message);
        }
        return result;

    }


    private DataSet addBindColumn(DataSet ds, string bindcolumn)
    {
        int ordinalposition = 0;
        if (ds != null)
        {
            if (ds.Tables.Count > 0)
            {
                if (ds.Tables[0].Rows.Count > 0)
                {
                    if (ds.Tables[0].Columns.Contains(bindcolumn))
                    {
                        ds.Tables[0].Columns.Add(bindcolumn + "_temp");

                        for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
                        {
                            ds.Tables[0].Rows[i][bindcolumn + "_temp"] = ds.Tables[0].Rows[i][bindcolumn];
                        }

                        ordinalposition = ds.Tables[0].Columns[bindcolumn].Ordinal;
                        ds.Tables[0].Columns.Remove(bindcolumn);

                        // foreach (DataColumn dc in ds.Tables[0].Columns)
                        for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
                        {
                            if (ds.Tables[0].Columns[j].Caption == bindcolumn + "_temp")
                            {
                                ds.Tables[0].Columns[j].Caption = bindcolumn;
                                ds.Tables[0].Columns[j].ColumnName = bindcolumn;
                                ds.Tables[0].Columns[j].SetOrdinal(ordinalposition);

                                ds.AcceptChanges();

                            }

                        }

                    }


                }
            }
        }
        return ds;
    }

    private string getSelectQuerywithPaging(DataTable dtaPage, DataTable dtaPageSrch, DataTable dtaPageKeys, DataTable dtaPageControls, string strParentField, string strChildField, string Condstr, ref string sFieldAliases, ref string iTotalRecord, bool Recorddetailsavail, ref string currentTotalRecordval, ref Hashtable hsDatatype, string sOrderby)
    {
        GenerateGrid objGG = new GenerateGrid();
        GridPaging gpobj = new GridPaging();
        string strselectsql = "";
        iTotalRecord = "0";
        string strKeyvalues = "";
        string strTableNames = objGG.GetTableNames(dtaPage, dtaPageSrch);
        if (strTableNames != "")
        {
            SecurityFilter objSec = new SecurityFilter();
            string sAFAdmin = objSec.QueryForAFAdmin();
            //-----------Added by 'Imran Gani' (copied from RakaTech) on 05-June-2013 for Org Security-----------
            HDDatabase HDD = new HDDatabase();
            string modelConString = "";
            modelConString = HttpContext.Current.Session["servercon"].ToString();
            string str_OwnerId = "";
            string str_BusinessUnit = "";
            if (HttpContext.Current.Session["dbcon"].ToString() != modelConString && sAFAdmin != "")
            {
                BusinessUnit.Access ACS = new BusinessUnit.Access(HttpContext.Current.Session["userid"].ToString(), HDD.GetConnection().ConnectionString, dtaPage.Rows[0]["page_id"].ToString());
                str_OwnerId = ACS.AccessableOwners;
                str_BusinessUnit = ACS.AccessableBuUnits;
            }
            string sCustodianFilter = ""; // = objSec.QueryForCustodianFilter();
            //----------------End for Org Security------------------
            if (sCustodianFilter.Trim() != "")
                if (!sCustodianFilter.StartsWith("or"))
                {
                    if (sAFAdmin.Trim() != "")
                    {
                        sAFAdmin = "(" + sAFAdmin + " and " + sCustodianFilter + ") ";
                    }
                    else
                    {
                        sAFAdmin = "(" + sCustodianFilter + ") ";
                    }
                }
                else
                {
                   
                    if (sAFAdmin.Trim() != "")
                    {
                        sAFAdmin = "(" + sAFAdmin + " " + sCustodianFilter + ") ";
                    }
                    else
                    {
                        sAFAdmin = "(" + sCustodianFilter + ") ";
                    }
                   
                }
            objSec = null;
            iTotalRecord = getTotalRecordsToFetch(strTableNames, Condstr);
            strselectsql = GetSelectPageFields(dtaPage, dtaPageSrch, dtaPageControls, strChildField, ref sFieldAliases, "CHILD", ref hsDatatype, strParentField);
            strKeyvalues = objGG.GetKeyValues(dtaPage, dtaPageSrch, dtaPageKeys);

            #region Old Lines commented by 'Imran Gani' on 14-Mar-2014, to avoid remaining record count.
            //if (Recorddetailsavail) // If the Node Contains the Record Count and Remaining Count Detail
            //{
            //    Int64 currentTotalRecord = Convert.ToInt64(currentTotalRecordval);
            //    currentTotalRecord = currentTotalRecord - GlobalValues.intNoOfRecordsPerPage;
            //    currentTotalRecordval = currentTotalRecord.ToString();
            //}
            //else
            //{
            //    currentTotalRecordval = iTotalRecord.ToString();
            //}
            #endregion

            #region Added by 'Imran Gani' on 14-Mar-2014, If the Node Contains the Record Count and to store current index.
            int iStartCount = int.Parse(currentTotalRecordval == "" ? "0" : currentTotalRecordval);
            int iEndCount = GlobalValues.intNoOfRecordsPerPage;
            if (Recorddetailsavail)
            {
                iEndCount = iStartCount + GlobalValues.intNoOfRecordsPerPage;
                iStartCount++;
                currentTotalRecordval = iEndCount.ToString();
            }
            else
                currentTotalRecordval = iEndCount.ToString();
            #endregion

            if (strselectsql != "")
            {
                #region Old Lines commented by 'Imran Gani' on 14-Mar-2014, to avoid top key from query and use ROW_NUMBER().
                //if (strKeyvalues != "")
                //{
                //    //strselectsql = "select Top " + iTotalRecord.ToString() + " " + strKeyvalues + " keys, " + "  " + strselectsql + " from " + strTableNames;
                //    strselectsql = "select Top " + currentTotalRecordval.ToString() + " " + strKeyvalues + " keys, " + "  " + strselectsql + " from " + strTableNames;
                //}
                //else
                //{
                //    strselectsql = "select " + strselectsql + " from " + strTableNames;
                //}
                #endregion

                #region Added by 'Imran Gani' on 14-Mar-2014, to use ROW_NUMBER() key in query.
                if (strKeyvalues != "")
                    strselectsql = "select " + strKeyvalues + " keys, " + strselectsql + ", ROW_NUMBER() OVER (ORDER BY " + sOrderby + " desc, " + strKeyvalues + " asc) AS [row] from " + strTableNames;
                else
                    strselectsql = "select " + strselectsql + ", ROW_NUMBER() OVER (ORDER BY " + sOrderby + " desc) AS [row] from " + strTableNames;
                #endregion
                    

                //-----------Added by 'Imran Gani' (copied from RakaTech) on 05-June-2013 for Org Security-----------
                string str_OrgSecurity = "";
                if (str_OwnerId != "" && dtaPageControls.Select("control_id='owner_id' or control_attrib_id='owner_id'").Length > 0)
                {
                    str_OrgSecurity += " " + str_OwnerId.Remove(0, 5) + " ";
                }
                str_OrgSecurity += (str_OrgSecurity == "" ? "" : " or owner_id is null ");
                if (str_BusinessUnit != "" && dtaPageControls.Select("control_id='bu_id' or control_attrib_id='bu_id'").Length > 0)
                {
                    str_OrgSecurity += " " + str_BusinessUnit + " ";
                }
                //----------------End for Org Security------------------
                
                if (sAFAdmin != "")
                {
                    if (dtaPage != null && dtaPage.Rows[0]["page_id"].ToString() == "p_sy_report") // Added by 'Imran Gani' on 01-Feb-2013, for report security per role.
                        strselectsql += " where (" + sAFAdmin + " or [" + dtaPage.Rows[0]["page_id"].ToString() + "].[af_row_id] in (select report_id from sy_report_acc where role_id = '" + HttpContext.Current.Session["roleid"] + "' and [is_accessible]=1 ))";
                    else
                        strselectsql += " where (" + sAFAdmin + " and " + str_OrgSecurity + ")";
                    //strselectsql += " where " + sAFAdmin; // commented by 'Imran Gani' on 05-June-2013 for Org Security
                }
               
                else
                {
                    strselectsql += " where 1=1 " + (str_OrgSecurity == "" ? "" : " and (" + str_OrgSecurity + ")");
                    //strselectsql += " where 1=1 "; // commented by 'Imran Gani' on 05-June-2013 for Org Security
                }

                if (Condstr != "")
                    strselectsql += " and " + Condstr;
                strselectsql = "Select " + sFieldAliases + ", keys from ( " + strselectsql + ") as tt  where [row] between " + iStartCount + " and " + iEndCount + " order by [row] asc";
            }

        }

        return strselectsql;
    }



    public string getTotalRecordsToFetch(string TableName, string CondStr)
    {
       
        HDD = new HDDatabase();
        string iTotalRecords = "0";
        try
        {

            string sSQL = "";
            sSQL = "select count(*) from " + TableName;
            if (CondStr != "")
                sSQL += " Where " + CondStr;
            DataSet dsTemp = HDD.GetDataset(sSQL);
            HDD = null;
            if (dsTemp.Tables.Count > 0)
            {
                if (dsTemp.Tables[0].Rows.Count > 0)
                {
                    iTotalRecords = dsTemp.Tables[0].Rows[0][0].ToString();
                }
            }

        }

        catch (Exception e1)
        {
        }
        return iTotalRecords.ToString();
    }
}
